import datetime
import math
import os
import time
import sys

# 将工程目录加入包扫描
currPath = os.path.abspath(os.path.dirname(__file__))
srcPath = os.path.split(currPath)[0]
rootPath = os.path.split(srcPath)[0]
projectPath = os.path.split(rootPath)[0]
sys.path.append(projectPath)

from src.db.FCDB import FCDB
from src.db.ProdDb import ProdDb
from src.utils import BatchUtils, CommonUtils
from src.utils.CommonUtils import cleanScreen
from src.utils.LogUtils import Logger


class SyncSecuritiesBaseInfo:
    """
    同步债券基础信息
    """

    def __init__(self, logger, localDb, fcdb, force):

        # 保存环境设置
        self.logger = logger
        self.localDb = localDb
        self.fcdb = fcdb
        self.size = 10000
        self.exchangeMap = {'001001': "", '001002': ".SH", '001003': '.SZ', '001005': '.IB', '001006': '.SH',
                            '001007': '.BC', '001018': '.SZ'}
        self.outsideRankMapping = {}
        self.compcodeMap = {}
        self.creditMap = {}
        self.force = force

    # 抽取本地债券最后同步时间
    def __queryDataDownloadtime__(self):
        sql = "SELECT max(DATA_DOWNLOAD_TIME) as TIME from deb_debenture_base"
        r = self.localDb.Query(sql)
        if r is None or len(r) == 0:
            return datetime.datetime.min()
        else:
            return r[0]["TIME"]

    @staticmethod
    def __queryOutsideRankMapping__():
        rankMapping = {
            "AAA": 1, "AAA-": 2,
            "AA+": 3, "AA": 4, "AA-": 5,
            "A+": 6, "A": 7, "A-": 8,
            "BBB+": 9, "BBB": 10, "BBB-": 11,
            "BB+": 12, "BB": 13, "BB-": 14,
            "B+": 15, "B": 16, "B-": 17,
            "CCC": 18, "CC": 19, "C": 20,
            "999": 999}
        return rankMapping;

    # 加载全部企业COMPCODE和OUTCODE
    def __queryCompanyCodes__(self):
        sql = "SELECT comp_code, credit_code FROM  ent_enterprise"
        return self.localDb.Query(sql)

    # 抽取数据源债券
    def __queryAllSecurities__(self):
        bondType2 = ['1011',
                     '521', '522', '523', '531', '532', '541', '542', '543', '590',
                     '611', '612', '613', '621', '622', '623', '624', '631', '632', '641', '651',
                     '911']
        sql = """
            SELECT a.SECURITYID, a.SECODE, a.ISSUECOMPCODE, a.BONDNAME, a.BONDSNAME, a.SYMBOL, a.EXCHANGE, a.BONDTYPE2, a.CALCAMODE,a.MATURITYYEAR,
            a.CURRENCYRATE ,a.RAISEMODE,a.ACTISSAMT,a.CURRENTAMT, a.CUR, a.STARTDATE,a.MATURITYDATE,a.LEADUWER, a.ASSLEADUWER, b.COUPONRATE,
            a.PERPAYDATE,a.ISSBEGDATE,a.CVTBDEXPIREMEMP,a.DATA_DOWNLOAD_TIME,a.ISGUARANTEE,a.NEWRATE
            from tq_bd_newestbasicinfo  a 
            left join tq_bd_basicinfo b  on a.SECODE  = b.SECODE AND b.ISVALID = 1
            WHERE a.ISVALID = 1 and a.BONDTYPE2 IN %s
        """

        return self.fcdb.Query(sql, (bondType2,))

    # 抽取债券招标情况
    def __queryTqBdBidInfo__(self):
        sql = """
            WITH base AS  (
                SELECT SECODE, FINALRATEWINBID, ROW_NUMBER() OVER ( PARTITION BY SECODE  ORDER BY DECLAREDATE DESC ) AS NO
                FROM TQ_BD_BIDINFO  WHERE FINALRATEWINBID IS NOT NULL
            )
            SELECT SECODE, FINALRATEWINBID FROM base WHERE NO = 1
        """
        return self.fcdb.Query(sql)

    def __querySecurityCredit__(self, secodes):
        sql = """
                SELECT SECODE, CREDITDATE, CREDITRATE
                FROM (
                    SELECT SECODE, CREDITDATE, CREDITRATE, ROW_NUMBER() OVER(PARTITION BY SECODE
                    ORDER BY CREDITDATE DESC) AS NO
                    FROM tq_bd_creditrate
                    WHERE RATETYPE = '1' AND SECODE IN %s
                ) t
                WHERE t.no = 1
        """
        return self.fcdb.Query(sql, (secodes,))

    # 获取本地库全部债券
    def __queryLocalSecurities__(self):
        sql = "SELECT id, SECODE,DATA_DOWNLOAD_TIME FROM deb_debenture_base"
        return self.localDb.Query(sql)

    # 确定是否存续债
    @staticmethod
    def __isContinuing__(securityData):
        if securityData is None:
            return 0
        if securityData["CURRENTAMT"] is None:
            return 0
        if securityData["CURRENTAMT"] == 0:
            return 0
        return 1

    # 构建数据对象
    def __buildUpdateData__(self, dataId, securityData, bidInfoList):

        outsideRank = "" if securityData['SECODE'] not in self.creditMap.keys() else self.creditMap[
            securityData['SECODE']]  # 债券外部评级
        outsideRankValue = 999 if outsideRank not in self.outsideRankMapping.keys() else self.outsideRankMapping.get(
            outsideRank)  # 债券外部评级值

        retData = [
            dataId,
            self.compcodeMap[securityData['ISSUECOMPCODE']] if securityData[
                                                                   'ISSUECOMPCODE'] in self.compcodeMap.keys() else "",
            # 发行人
            securityData['ISSUECOMPCODE'],  # 发行人COMPCODE
            securityData['BONDNAME'],  # 债券全称
            securityData['BONDSNAME'],  # 债券简称
            "%s%s" % (securityData['SYMBOL'], self.exchangeMap[securityData['EXCHANGE']] if securityData[
                                                                                                'EXCHANGE'] in self.exchangeMap.keys() else ""),
            # 债券代码
            0,  # 是否城投债（0否  1是）
            securityData['BONDTYPE2'],  # 债券类型
            securityData['CALCAMODE'],  # 利率类型（1.固定利率  2.浮动利率）
            securityData['MATURITYYEAR'],  # 债券期限
            securityData['CURRENCYRATE'],  # 当前利率（%）
            securityData['COUPONRATE'],  # 票面利率（发行利率）（%）
            securityData['RAISEMODE'],  # 发行方法（1.公募  2.私募）
            securityData['ACTISSAMT'],  # 发行规模
            securityData['CURRENTAMT'],  # 当前规模
            self.__isContinuing__(securityData),  # 是否存续债
            1 if securityData['CUR'] == 'CNY' else 2,  # 币种（1.人民币  2.美元）
            securityData['EXCHANGE'],  # 交易市场
            securityData['STARTDATE'],  # 起息日
            securityData['MATURITYDATE'],  # 到期日
            securityData['LEADUWER'],  # 主承销商
            securityData['ASSLEADUWER'],  # 副承销商
            securityData['SECURITYID'],  # 财汇证券内码
            securityData['SECODE'],  # 财汇 SECODE
            securityData['DATA_DOWNLOAD_TIME'],  # 财汇 数据下载时间
            0,
            outsideRank,  # 债券外部评级
            outsideRankValue,  # 债券外部评级值
            securityData['PERPAYDATE'],  # 每年付息日
            securityData['ISSBEGDATE'],  # 发行起始日
            securityData['CVTBDEXPIREMEMP'],  # 含权期限
            securityData['NEWRATE'],  # 债项评级
            securityData['ISGUARANTEE'],  # 是否担保

        ]

        return tuple(retData)

    # 更新债券
    def __updateSecurities__(self, updateDatas):
        sql = """
            INSERT INTO deb_debenture_base (id,issuer_code,issue_comp_code,deb_name,short_name,deb_code,city_investment,deb_type,
            rate_type,deb_time,issuing_rate,coupon_rate,issuing_method,`scale`,remaining_sum,is_continuing,currency,market,
            starting_interest_date,expire_date,underwriters,deputy_underwriter,security_id,secode,data_download_time,
            delete_flag,outside_level,outside_level_value,perpay_date,issue_begin_date,deb_time_option,new_rate,is_guarantee) 
            VALUES (%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s,%s) AS new
            ON DUPLICATE KEY UPDATE 
            issuer_code=new.issuer_code,
            issue_comp_code=new.issue_comp_code,
            deb_name=new.deb_name,
            short_name=new.short_name,
            deb_code=new.deb_code,
            city_investment=new.city_investment,
            deb_type=new.deb_type,
            rate_type=new.rate_type,
            deb_time=new.deb_time,
            issuing_rate=new.issuing_rate,
            coupon_rate=new.coupon_rate,
            issuing_method=new.issuing_method,
            `scale`=new.`scale`,
            remaining_sum=new.remaining_sum,
            is_continuing=new.is_continuing,
            currency=new.currency,
            market=new.market,
            starting_interest_date=new.starting_interest_date,
            expire_date=new.expire_date,
            underwriters=new.underwriters,
            deputy_underwriter=new.deputy_underwriter,
            security_id=new.security_id,
            secode=new.secode,
            data_download_time=new.data_download_time,
            delete_flag=new.delete_flag,
            outside_level=new.outside_level,
            outside_level_value=new.outside_level_value,
            perpay_date = new.perpay_date,
            issue_begin_date = new.issue_begin_date,
            deb_time_option = new.deb_time_option,
            new_rate = new.new_rate,
            is_guarantee = new.is_guarantee
        """
        return self.localDb.ExecMany(sql, updateDatas)

    # 删除 财汇数据源不存在的数据
    def __deleteInvalidSecodes__(self, secodes):
        sql = """
            delete from deb_debenture_base where secode in %s
        """
        return self.localDb.ExecNoneQuery(sql, (secodes,))

    def __clearDuplicated__(self):
        sql = """
        WITH invalid_ids AS (
            SELECT min(id) FROM  deb_debenture_base GROUP BY secode HAVING COUNT(1) > 1
        )
        delete from deb_debenture_base WHERE id IN (SELECT * FROM invalid_ids)
        """
        return self.localDb.ExecNoneQuery(sql)

    # 同步债券基本信息
    def sync(self):
        if self.force:
            self.logger.warn(self.logger.__colorText__("强制更新模式！！！", "red"))

        self.logger.info("同步债券基本信息 开始")

        # 获取数据源债券
        t = time.perf_counter()

        self.logger.info("从数据源加载债券")
        remoteList = self.__queryAllSecurities__()
        remoteList = {kv['SECODE']: kv for kv in remoteList}
        self.logger.info("数据源有效债券: %d" % len(remoteList))
        cast = (time.perf_counter() - t) * 1000
        self.logger.info('耗时: %.2f 毫秒' % cast)

        self.logger.info("从数据源加载债券招标情况")
        bidInfoList = self.__queryTqBdBidInfo__()
        bidInfoList = {kv['SECODE']: kv['FINALRATEWINBID'] for kv in bidInfoList}
        self.logger.info("数据源有效数据: %d" % len(bidInfoList))
        cast = (time.perf_counter() - t) * 1000
        self.logger.info('耗时: %.2f 毫秒' % cast)

        # 加载外部评级映射
        self.logger.info("加载外部评级映射")
        self.outsideRankMapping = self.__queryOutsideRankMapping__()
        self.logger.info("加载外部评级映射: %d" % len(self.outsideRankMapping))
        cast = (time.perf_counter() - t) * 1000
        self.logger.info('耗时: %.2f 毫秒' % cast)

        # 加载全部企业代码
        t = time.perf_counter()
        self.logger.info("从数据库加载企业代码和外部代码。")
        compCodes = self.__queryCompanyCodes__()
        self.compcodeMap = {kv['comp_code']: kv['credit_code'] for kv in compCodes}
        self.logger.info("数据库加载 全部企业代码和外部代码: %d" % len(self.compcodeMap))
        cast = (time.perf_counter() - t) * 1000
        self.logger.info('耗时: %.2f 毫秒' % cast)

        # 获取本地库全部债券
        t = time.perf_counter()
        self.logger.info("从本地加载债券")
        localList = self.__queryLocalSecurities__()
        localList = {kv['SECODE']: kv for kv in localList}
        self.logger.info("本地数据（全部）: %d" % len(localList))
        cast = (time.perf_counter() - t) * 1000
        self.logger.info('耗时: %.2f 毫秒' % cast)

        # 获取外部评级
        t = time.perf_counter()
        self.logger.info("从财汇加载债券外部评级")
        secodes = list(remoteList.keys())
        creditList = BatchUtils.batchQuery(self.__querySecurityCredit__, secodes, self.size)
        self.creditMap = {kv['SECODE']: kv["CREDITRATE"] for kv in creditList}
        self.logger.info("外部评级数据: %d" % len(creditList))
        cast = (time.perf_counter() - t) * 1000
        self.logger.info('耗时: %.2f 毫秒' % cast)

        # # 清理财汇已删除的数据
        # invalidSecodes = list(set(localList.keys()) - set(remoteList.keys()))
        # if len(invalidSecodes) > 0:
        #     self.__deleteInvalidSecodes__(invalidSecodes)
        #     self.logger.warn("删除无效SECODE %d" % len(invalidSecodes))

        # 相同的，检查更新时间戳
        self.logger.info("生成更新数据集")
        t = time.perf_counter()
        updateDatas = []
        for secode in remoteList.keys():
            if secode not in localList.keys():
                updateDatas.append(self.__buildUpdateData__(None, remoteList[secode], bidInfoList))
            elif self.force or localList[secode]['DATA_DOWNLOAD_TIME'] is None or localList[secode][
                'DATA_DOWNLOAD_TIME'] < remoteList[secode]['DATA_DOWNLOAD_TIME']:
                updateDatas.append(self.__buildUpdateData__(localList[secode]["id"], remoteList[secode], bidInfoList))

        if len(updateDatas) > 0:
            BatchUtils.batchInsert(self.__updateSecurities__, updateDatas)
            self.logger.info("更新数据: %d" % len(updateDatas))
            cast = (time.perf_counter() - t) * 1000
            self.logger.info('耗时: %.2f 毫秒' % cast)
        else:
            self.logger.info("无更新数据")

        # 清理重复数据（同secode多条数据，主要出现于新债)
        cnt = self.__clearDuplicated__()
        self.logger.info("清理重复数据 %d" % cnt)

        self.logger.info("同步债券基本信息 完成")


# 主函数
def main(argv):
    logger = Logger()

    localDb = ProdDb(logSql=False, autocommit=True)
    fcdb = FCDB(logSql=False, autocommit=True)

    cleanScreen()
    t = time.perf_counter()
    logger.info("========== 债券数据处理 0.0.1 ==========")

    # 提取参数 是否强制更新模式
    isForce = CommonUtils.isCommandParam(argv, "force")["exist"]

    # 基础信息
    baseInfo = SyncSecuritiesBaseInfo(logger=logger, localDb=localDb, fcdb=fcdb, force=isForce)
    baseInfo.sync()

    cast = (time.perf_counter() - t)
    m = math.floor(cast / 60)
    s = cast - m * 60
    logger.info('========== 同步结束 %s 总耗时: %d分%.3f秒 ==========' % (datetime.datetime.now(), m, s))


# 程序入口
# 参数 --force 全部更新
if __name__ == "__main__":
    main(sys.argv)
